<?php

namespace app\index\controller;

use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use app\index\validate\Valid;
use PHPExcel;
use PHPExcel_Writer_Excel5;
use PHPExcel_Style_Alignment;
use PHPExcel_Style_Fill;
use PHPExcel_Style_Border;
use PHPExcel_Cell_DataType;
class Rcexecel extends Common
{
	//部门和所有统计的导出
	public function tongji($did=null){
		if($did){
			$where="ap.is_deleted=0 and ap.is_actived=1 and d.id={$did} and de.index=1 and ap.state=7 ";
		}else{
			$where="ap.is_deleted=0 and ap.is_actived=1 and de.index=1 and ap.state=7 ";
		}
		$apply=Db::table("rc_baoxiao_apply")->alias("ap")
		->join("rc_baoxiao_detail apd","ap.id=apd.aid")
		->join("employee ce","ap.cuser=ce.id")
		->join("department_employee de","de.employee_id=ce.id")
		->join("department d","de.department_id=d.id")
		->join("employee eb","ap.baoxiaoren=eb.id")
		->join("cg_kaoheqi2 k2","k2.id=ap.kaohe")
		->join("cg_kaoheqi1 k1","k1.id=k2.pid")
		->join("cg_cainian cn","k1.cid=cn.id")
		->join("cg_feiyongkemu3 f3","apd.feiid=f3.id")
		->join("cg_feiyongkemu2 f2","f3.pid=f2.id")
		->join("cg_feiyongkemu1 f1","f2.pid=f1.id")
		->join("cg_fijiruzd fj","ap.jrbm=fj.id")
		->join("department dfj", "fj.title=dfj.id")
		->join("cg_jxs j","apd.jxs=j.id","LEFT")
		->where($where)->order("ap.id desc,apd.id")
		->field("ap.id,ap.no,ce.em_name as cman,eb.em_name as bman,ap.ctime,ap.guidangdate,apd.start,apd.end,apd.chufa,apd.mudi,cn.title as nian,k2.title as yue,f1.title as kemu1,f2.title as kemu2,f3.title as kemu3,dfj.de_name as jrdap,j.title as jxs,apd.remark,apd.pizhunfeiyong")
		->select();
		for($i=0;$i<count($apply);$i++){
			$apply[$i]["ctime"]=date("Y-m-d",$apply[$i]["ctime"]);
			$apply[$i]["guidangdate"]=date("Y-m-d",strtotime($apply[$i]["guidangdate"]));
		}
		if($did)
			$biaoti="部门日常费用统计";
		else
			$biaoti="所有日常费用统计";
			$eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();
	  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($biaoti);
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
 

       

        
          $objPHPExcel->getActiveSheet()->mergeCells('A1:S1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1', $biaoti);
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:S2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);


                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3:S3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3:S3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A3:S3')->getFont()->setSize(11);
         $objPHPExcel->getActiveSheet()->getStyle('A3:S3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A3:S3')->getFill()->getStartColor()->setARGB('0099CCFF');

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"报销单号");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"申请人");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',"费用使用人");
        $objPHPExcel->getActiveSheet()->setCellValue('E3',"提交时间");
        $objPHPExcel->getActiveSheet()->setCellValue('F3',"归档时间");
        $objPHPExcel->getActiveSheet()->setCellValue('G3',"开始时间");
        $objPHPExcel->getActiveSheet()->setCellValue('H3',"结束时间");
        $objPHPExcel->getActiveSheet()->setCellValue('I3',"出发地点");
        $objPHPExcel->getActiveSheet()->setCellValue('J3',"到达地点");
        $objPHPExcel->getActiveSheet()->setCellValue('K3',"归入财年");
        $objPHPExcel->getActiveSheet()->setCellValue('L3',"归入考核期");
        $objPHPExcel->getActiveSheet()->setCellValue('M3',"一级费用科目");
        $objPHPExcel->getActiveSheet()->setCellValue('N3',"二级费用科目");
        $objPHPExcel->getActiveSheet()->setCellValue('O3',"三级费用科目");
        $objPHPExcel->getActiveSheet()->setCellValue('P3',"费用计入部门");
        $objPHPExcel->getActiveSheet()->setCellValue('Q3',"费用计入经销商");
        $objPHPExcel->getActiveSheet()->setCellValue('R3',"费用说明");
        $objPHPExcel->getActiveSheet()->setCellValue('S3',"报销金额");

        $h=3;
        for($i=0;$i<count($apply);$i++){
        	$h++;$vo=$apply[$i];
        	$objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('B'.$h,$vo["no"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('C'.$h,$vo["cman"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('D'.$h,$vo["bman"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('E'.$h,$vo["ctime"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('F'.$h,$vo["guidangdate"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('G'.$h,$vo["start"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('H'.$h,$vo["end"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('I'.$h,$vo["chufa"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('J'.$h,$vo["mudi"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('K'.$h,$vo["nian"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('L'.$h,$vo["yue"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('M'.$h,$vo["kemu1"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('N'.$h,$vo["kemu2"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('O'.$h,$vo["kemu3"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('P'.$h,$vo["jrdap"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('Q'.$h,$vo["jxs"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('R'.$h,$vo["remark"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('S'.$h,$vo["pizhunfeiyong"],PHPExcel_Cell_DataType::TYPE_STRING);

        	         //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':S'.$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':S'.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':S'.$h)->getFont()->setSize(11);
        }
         	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('F')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('G')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('H')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('I')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('J')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('K')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('L')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('M')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('N')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('O')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('P')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('Q')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('R')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('S')->setWidth(20);	
            $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:S'.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        



        	  $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename='.$biaoti.'.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}
	//科目汇总
	public function kemu($cn=null){
		if($cn)
			$year=Db::table("cg_cainian")->where("id={$cn}")->value("title");
		else{
			$now=time();
			$cn=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("id");
			$year=Db::table("cg_cainian")->where("starttime<=$now and endtime>=$now")->value("title");
		}
		$where="ap.is_deleted=0 and ap.is_actived=1  and ap.state=7 and cn.id={$cn} ";
		$kaohe=Db::table("cg_kaoheqi2")->alias("k2")
		->join("cg_kaoheqi1 k1","k1.id=k2.pid")
		->join("cg_cainian cn","k1.cid=cn.id")
		->where("cn.title='{$year}' or cn.title='{$year}年'")
		->order("k2.starttime")
		->field("k2.title,k2.id")
		->select();
		$kaoc=count($kaohe);
		$sumcol=$kaoc+4;
		$sumcolm=chr($sumcol+96);
		$karr=[];
		for($i=0;$i<count($kaohe);$i++){
			$karr[]=$kaohe[$i]["id"];
		}
		$kstr=implode(',',$karr);

		$zong=Db::table("rc_baoxiao_apply")->alias("ap")
			->join("rc_baoxiao_detail apd","ap.id=apd.aid")
			->join("employee eb","ap.baoxiaoren=eb.id")
			->join("cg_kaoheqi2 k2","k2.id=ap.kaohe")
			->join("cg_kaoheqi1 k1","k1.id=k2.pid")
			->join("cg_cainian cn","k1.cid=cn.id")
			->join("cg_feiyongkemu3 f3","apd.feiid=f3.id")
			->join("cg_feiyongkemu2 f2","f3.pid=f2.id")
			->join("cg_feiyongkemu1 f1","f2.pid=f1.id")
			->group("f3.id")
			->where($where)
			->order("sum(apd.feis) desc")
			->field("f3.title as kemu3,f2.title as kemu2,f1.title as kemu1,sum(apd.feis) as jine,f3.id as kemu")
			->select();
		for($i=0;$i<count($zong);$i++){
			$zong[$i]["detail"]=Db::table("rc_baoxiao_apply")->alias("ap")->join("rc_baoxiao_detail apd","ap.id=apd.aid")
							->join("cg_feiyongkemu3 f3","apd.feiid=f3.id")
							->join("cg_feiyongkemu2 f2","f3.pid=f2.id")
							->join("cg_feiyongkemu1 f1","f2.pid=f1.id")
							->group("ap.kaohe")
							->where("ap.kaohe in ($kstr) and f3.id={$zong[$i]["kemu"]} and ap.is_deleted=0 and ap.is_actived=1 and ap.state=7")
							->field("ap.kaohe,sum(apd.feis) as jine")->select();

		}

		$eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();

	  Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($year."财年费用科目日常报销汇总");
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
 

       

        
          $objPHPExcel->getActiveSheet()->mergeCells('A1:'.$sumcolm.'1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1',$year."财年费用科目日常报销汇总");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:'.$sumcolm.'2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);

                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$sumcolm.'3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$sumcolm.'3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$sumcolm.'3')->getFont()->setSize(11);
         $objPHPExcel->getActiveSheet()->getStyle('A3:'.$sumcolm.'3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$sumcolm.'3')->getFill()->getStartColor()->setARGB('0099CCFF');

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"二级科目");
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"三级科目");
        for($i=0;$i<count($kaohe);$i++){
        	$kaohe[$i]["heji"]=0;
        	$objPHPExcel->getActiveSheet()->setCellValue(chr($i+100).'3',$kaohe[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $objPHPExcel->getActiveSheet()->setCellValue($sumcolm.'3',"合计");

        $h=3;
        $zonghe=0;
        for($i=0;$i<count($zong);$i++){
        	$h++;$vo=$zong[$i];
        	$objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
	        $objPHPExcel->getActiveSheet()->setCellValue('B'.$h,$vo["kemu2"],PHPExcel_Cell_DataType::TYPE_STRING);
	        $objPHPExcel->getActiveSheet()->setCellValue('C'.$h,$vo["kemu3"],PHPExcel_Cell_DataType::TYPE_STRING);
	        $zonge=0;
	        for($j=0;$j<count($kaohe);$j++){
	        	
	        	for($k=0;$k<count($vo["detail"]);$k++){
	        		if($kaohe[$j]["id"]==$vo["detail"][$k]["kaohe"]){
	        			$objPHPExcel->getActiveSheet()->setCellValue(chr($j+100).$h,$vo["detail"][$k]["jine"],PHPExcel_Cell_DataType::TYPE_STRING);
	        			$zonge+=$vo["detail"][$k]["jine"];
	        			$kaohe[$j]["heji"]+=$vo["detail"][$k]["jine"];
	        			$zonghe+=$vo["detail"][$k]["jine"];
	        		}
	        	}
	        }
	        $objPHPExcel->getActiveSheet()->setCellValue($sumcolm.$h,$zonge,PHPExcel_Cell_DataType::TYPE_STRING);
	        	         //水平剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$sumcolm.$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	            //垂直剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$sumcolm.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	        //字体
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$sumcolm.$h)->getFont()->setSize(11);
	        $objPHPExcel->getActiveSheet()->getStyle($sumcolm.$h)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        	$objPHPExcel->getActiveSheet()->getStyle($sumcolm.$h)->getFill()->getStartColor()->setARGB('00FAF2CC');

        }

        	$hj=$h+1;
        	$objPHPExcel->getActiveSheet()->mergeCells('A'.$hj.':C'.$hj);
        	$objPHPExcel->getActiveSheet()->setCellValue('A'.$hj,"合计");
        	$objPHPExcel->getActiveSheet()->getStyle('A'.$hj)->getFont()->setBold(true);
        	for($i=0;$i<count($kaohe);$i++){
        		
        		$objPHPExcel->getActiveSheet()->setCellValue(chr($i+100).$hj,$kaohe[$i]["heji"],PHPExcel_Cell_DataType::TYPE_STRING);
	        }
	        $objPHPExcel->getActiveSheet()->setCellValue($sumcolm.$hj,$zonghe,PHPExcel_Cell_DataType::TYPE_STRING);
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':'.$sumcolm.$hj)->getFont()->setBold(true);
	         	         //水平剧中
	        $objPHPExcel->getActiveSheet()->getStyle('D'.$hj.':'.$sumcolm.$hj)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	            //垂直剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':'.$sumcolm.$hj)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	        //字体
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':'.$sumcolm.$hj)->getFont()->setSize(11);
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':'.$sumcolm.$hj)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        	$objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':'.$sumcolm.$hj)->getFill()->getStartColor()->setARGB('00FFCC99');
        	 $objPHPExcel->getActiveSheet()->getStyle('A'.$hj)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        	 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
         	 for($i=0;$i<count($kaohe);$i++){
         	 	$objPHPExcel->getActiveSheet()->getColumnDimension(chr($i+100))->setWidth(20);
	        }
	        $objPHPExcel->getActiveSheet()->getColumnDimension($sumcolm)->setWidth(20);

	           $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:'.$sumcolm.$hj)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        


        	  $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
        header("Pragma: public");
        header("Expires:0");
        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
        header("Content-Type:application/force-download");
        header("Content-Type:application/vnd.ms-execl");
        header("Content-Type:application/octet-stream");
        header("Content-Type:application/download");
        header('Content-Disposition:attachment;filename='.$year.'财年费用科目日常报销汇总.xls');
        header("Content-type:text/html;charset=utf-8");
        header("Content-Transfer-Encoding:binary");
        
        $objWriter->save('php://output');
	}
	//部门汇总
	public function bm($year){
		
		if($year){
			$year=(int)trim($year);
		}else{
			$year=date("Y");
		}
		$where="ap.is_deleted=0 and ap.is_actived=1  and ap.state=7 and (cn.title='{$year}' or cn.title='{$year}年') and de.index=1 ";	
		$kaohe=Db::table("cg_kaoheqi2")->alias("k2")
		->join("cg_kaoheqi1 k1","k1.id=k2.pid")
		->join("cg_cainian cn","k1.cid=cn.id")
		->where("cn.title='{$year}' or cn.title='{$year}年'")
		->order("k2.starttime")
		->field("k2.title,k2.id")
		->select();
		$kaoc=count($kaohe);
		
		$karr=[];
		for($i=0;$i<count($kaohe);$i++){
			$karr[]=$kaohe[$i]["id"];
		}
		$kstr=implode(',',$karr);
		$sumcol=$kaoc+3;
		$sumcolm=chr($sumcol+96);
			$zong=Db::table("rc_baoxiao_apply")->alias("ap")
			->join("employee ce","ap.cuser=ce.id")
			->join("department_employee de","de.employee_id=ce.id")
			->join("department d","de.department_id=d.id")
			->join("employee eb","ap.baoxiaoren=eb.id")
			->join("cg_kaoheqi2 k2","k2.id=ap.kaohe")
			->join("cg_kaoheqi1 k1","k1.id=k2.pid")
			->join("cg_cainian cn","k1.cid=cn.id")
			->group("d.id")
			->where($where)
			->order("sum(ap.pizhunjin) desc")
			->field("d.de_name,sum(ap.pizhunjin) as jine,d.id as did")
			->select();
		

		for($i=0;$i<count($zong);$i++){
			$zong[$i]["detail"]=Db::table("rc_baoxiao_apply")->alias("ap")->join("rc_baoxiao_detail apd","ap.id=apd.aid")
							->join("employee ce","ap.cuser=ce.id")
							->join("department_employee de","de.employee_id=ce.id")
							->join("department d","de.department_id=d.id")
							->group("ap.kaohe")
							->where("ap.kaohe in ($kstr) and d.id={$zong[$i]["did"]} and de.index=1 and ap.is_deleted=0 and ap.is_actived=1 and ap.state=7")
							->field("ap.kaohe,sum(apd.feis) as jine")->select();

		}	
			$eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();

	  	Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($year."财年部门日常报销汇总");
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
 

       

        
          $objPHPExcel->getActiveSheet()->mergeCells('A1:'.$sumcolm.'1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1',$year."财年部门日常报销汇总");
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:'.$sumcolm.'2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);
                  //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$sumcolm.'3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$sumcolm.'3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$sumcolm.'3')->getFont()->setSize(11);
         $objPHPExcel->getActiveSheet()->getStyle('A3:'.$sumcolm.'3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$sumcolm.'3')->getFill()->getStartColor()->setARGB('0099CCFF');

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"部门");
        for($i=0;$i<count($kaohe);$i++){
        	$kaohe[$i]["heji"]=0;
        	$objPHPExcel->getActiveSheet()->setCellValue(chr($i+99).'3',$kaohe[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $objPHPExcel->getActiveSheet()->setCellValue($sumcolm.'3',"合计");
         $h=3;
        $zonghe=0;

        for($i=0;$i<count($zong);$i++){
        	$h++;$vo=$zong[$i];
        	$objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
	        $objPHPExcel->getActiveSheet()->setCellValue('B'.$h,$vo["de_name"],PHPExcel_Cell_DataType::TYPE_STRING);
	        $zonge=0;
	        for($j=0;$j<count($kaohe);$j++){
	        	
	        	for($k=0;$k<count($vo["detail"]);$k++){
	        		if($kaohe[$j]["id"]==$vo["detail"][$k]["kaohe"]){
	        			$objPHPExcel->getActiveSheet()->setCellValue(chr($j+99).$h,$vo["detail"][$k]["jine"],PHPExcel_Cell_DataType::TYPE_STRING);
	        			$zonge+=$vo["detail"][$k]["jine"];
	        			$kaohe[$j]["heji"]+=$vo["detail"][$k]["jine"];
	        			$zonghe+=$vo["detail"][$k]["jine"];
	        		}
	        	}
	        }
	        $objPHPExcel->getActiveSheet()->setCellValue($sumcolm.$h,$zonge,PHPExcel_Cell_DataType::TYPE_STRING);
	        	         //水平剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$sumcolm.$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	            //垂直剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$sumcolm.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	        //字体
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$sumcolm.$h)->getFont()->setSize(11);
	        $objPHPExcel->getActiveSheet()->getStyle($sumcolm.$h)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        	$objPHPExcel->getActiveSheet()->getStyle($sumcolm.$h)->getFill()->getStartColor()->setARGB('00FAF2CC');
        }
        		$hj=$h+1;
        	$objPHPExcel->getActiveSheet()->mergeCells('A'.$hj.':B'.$hj);
        	$objPHPExcel->getActiveSheet()->setCellValue('A'.$hj,"合计");
        	$objPHPExcel->getActiveSheet()->getStyle('A'.$hj)->getFont()->setBold(true);
        	for($i=0;$i<count($kaohe);$i++){
        		
        		$objPHPExcel->getActiveSheet()->setCellValue(chr($i+99).$hj,$kaohe[$i]["heji"],PHPExcel_Cell_DataType::TYPE_STRING);
	        }
	        $objPHPExcel->getActiveSheet()->setCellValue($sumcolm.$hj,$zonghe,PHPExcel_Cell_DataType::TYPE_STRING);
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':'.$sumcolm.$hj)->getFont()->setBold(true);
	         	         //水平剧中
	        $objPHPExcel->getActiveSheet()->getStyle('C'.$hj.':'.$sumcolm.$hj)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	            //垂直剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':'.$sumcolm.$hj)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	        //字体
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':'.$sumcolm.$hj)->getFont()->setSize(11);
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':'.$sumcolm.$hj)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        	$objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':'.$sumcolm.$hj)->getFill()->getStartColor()->setARGB('00FFCC99');
        	 $objPHPExcel->getActiveSheet()->getStyle('A'.$hj)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        	 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
         	 for($i=0;$i<count($kaohe);$i++){
         	 	$objPHPExcel->getActiveSheet()->getColumnDimension(chr($i+99))->setWidth(20);
	        }
	        $objPHPExcel->getActiveSheet()->getColumnDimension($sumcolm)->setWidth(20);

	           $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:'.$sumcolm.$hj)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        
        	  $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
	        header("Pragma: public");
	        header("Expires:0");
	        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
	        header("Content-Type:application/force-download");
	        header("Content-Type:application/vnd.ms-execl");
	        header("Content-Type:application/octet-stream");
	        header("Content-Type:application/download");
	        header('Content-Disposition:attachment;filename='.$year.'财年部门日常报销汇总.xls');
	        header("Content-type:text/html;charset=utf-8");
	        header("Content-Transfer-Encoding:binary");
	        
	        $objWriter->save('php://output');

        
	}
	//导出人员
	public function renyuan($year,$did=null){
		if(trim($year)){
			$year=(int)trim($year);
		}else{
			$year=date("Y");
		}
		if($did){
			$where="ap.is_deleted=0 and ap.is_actived=1 and d.id={$did} and de.index=1 and ap.state=7 and (cn.title='{$year}' or cn.title='{$year}年') ";
		}else{
			$where="ap.is_deleted=0 and ap.is_actived=1 and de.index=1 and ap.state=7 and (cn.title='{$year}' or cn.title='{$year}年') ";
		}
		$kaohe=Db::table("cg_kaoheqi2")->alias("k2")
		->join("cg_kaoheqi1 k1","k1.id=k2.pid")
		->join("cg_cainian cn","k1.cid=cn.id")
		->where("cn.title='{$year}' or cn.title='{$year}年'")
		->order("k2.starttime")
		->field("k2.title,k2.id")
		->select();
		$kaoc=count($kaohe);
		
		$karr=[];
		for($i=0;$i<count($kaohe);$i++){
			$karr[]=$kaohe[$i]["id"];
		}
		$kstr=implode(',',$karr);
		$sumcol=$kaoc+3;
		$sumcolm=chr($sumcol+96);
		$zong=Db::table("rc_baoxiao_apply")->alias("ap")
			->join("employee ce","ap.cuser=ce.id")
			->join("department_employee de","de.employee_id=ce.id")
			->join("department d","de.department_id=d.id")
			->join("employee eb","ap.baoxiaoren=eb.id")
			->join("cg_kaoheqi2 k2","k2.id=ap.kaohe")
			->join("cg_kaoheqi1 k1","k1.id=k2.pid")
			->join("cg_cainian cn","k1.cid=cn.id")
			->group("eb.id")
			->where($where)
			->order("sum(ap.pizhunjin) desc")
			->field("eb.em_name,sum(ap.pizhunjin) as jine,eb.id as bman")
			->select();
		for($i=0;$i<count($zong);$i++){
			$zong[$i]["detail"]=Db::table("rc_baoxiao_apply")->alias("ap")->join("rc_baoxiao_detail apd","ap.id=apd.aid")
							->group("ap.kaohe")
							->where("ap.kaohe in ($kstr) and ap.baoxiaoren={$zong[$i]["bman"]} and ap.is_deleted=0 and ap.is_actived=1 and ap.state=7")
							->field("ap.kaohe,sum(apd.feis) as jine")->select();

		}

			$eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();

    		if($did){
    			$biaoti=$year."财年本部门人员日常报销汇总";
    		}else{
    			$biaoti=$year."财年人员日常报销汇总";
    		}
	  	Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle($biaoti);
              #标题
        $objPHPExcel->getActiveSheet()->getRowDimension('1')->setRowHeight(20);
        $objPHPExcel->getActiveSheet()->getRowDimension('2')->setRowHeight(20);
 

       

        
          $objPHPExcel->getActiveSheet()->mergeCells('A1:'.$sumcolm.'1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1',$biaoti);
              //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1')->getFont()->setSize(14);

            #################表头信息
        $objPHPExcel->getActiveSheet()->mergeCells('A2:'.$sumcolm.'2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"(本表单由软件导出，导出时间：".date("Y-m-d H:i:s")."；导出人：{$users["em_name"]})");
                //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setBold(true);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A2')->getFont()->setSize(14);
                   //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$sumcolm.'3')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$sumcolm.'3')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$sumcolm.'3')->getFont()->setSize(11);
         $objPHPExcel->getActiveSheet()->getStyle('A3:'.$sumcolm.'3')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A3:'.$sumcolm.'3')->getFill()->getStartColor()->setARGB('0099CCFF');

        $objPHPExcel->getActiveSheet()->setCellValue('A3',"序号");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',"姓名");
        for($i=0;$i<count($kaohe);$i++){
        	$kaohe[$i]["heji"]=0;
        	$objPHPExcel->getActiveSheet()->setCellValue(chr($i+99).'3',$kaohe[$i]["title"],PHPExcel_Cell_DataType::TYPE_STRING);
        }
        $objPHPExcel->getActiveSheet()->setCellValue($sumcolm.'3',"合计");
        $h=3;
        $zonghe=0;

        for($i=0;$i<count($zong);$i++){
        	$h++;$vo=$zong[$i];
        	$objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$i+1,PHPExcel_Cell_DataType::TYPE_STRING);
	        $objPHPExcel->getActiveSheet()->setCellValue('B'.$h,$vo["em_name"],PHPExcel_Cell_DataType::TYPE_STRING);
	        $zonge=0;
	        for($j=0;$j<count($kaohe);$j++){
	        	
	        	for($k=0;$k<count($vo["detail"]);$k++){
	        		if($kaohe[$j]["id"]==$vo["detail"][$k]["kaohe"]){
	        			$objPHPExcel->getActiveSheet()->setCellValue(chr($j+99).$h,$vo["detail"][$k]["jine"],PHPExcel_Cell_DataType::TYPE_STRING);
	        			$zonge+=$vo["detail"][$k]["jine"];
	        			$kaohe[$j]["heji"]+=$vo["detail"][$k]["jine"];
	        			$zonghe+=$vo["detail"][$k]["jine"];
	        		}
	        	}
	        }
	        $objPHPExcel->getActiveSheet()->setCellValue($sumcolm.$h,$zonge,PHPExcel_Cell_DataType::TYPE_STRING);
	        	         //水平剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$sumcolm.$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	            //垂直剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$sumcolm.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	        //字体
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$sumcolm.$h)->getFont()->setSize(11);
	        $objPHPExcel->getActiveSheet()->getStyle($sumcolm.$h)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        	$objPHPExcel->getActiveSheet()->getStyle($sumcolm.$h)->getFill()->getStartColor()->setARGB('00FAF2CC');
        }
        	$hj=$h+1;
        	$objPHPExcel->getActiveSheet()->mergeCells('A'.$hj.':B'.$hj);
        	$objPHPExcel->getActiveSheet()->setCellValue('A'.$hj,"合计");
        	$objPHPExcel->getActiveSheet()->getStyle('A'.$hj)->getFont()->setBold(true);
        	for($i=0;$i<count($kaohe);$i++){
        		
        		$objPHPExcel->getActiveSheet()->setCellValue(chr($i+99).$hj,$kaohe[$i]["heji"],PHPExcel_Cell_DataType::TYPE_STRING);
	        }
	        $objPHPExcel->getActiveSheet()->setCellValue($sumcolm.$hj,$zonghe,PHPExcel_Cell_DataType::TYPE_STRING);
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':'.$sumcolm.$hj)->getFont()->setBold(true);
	         	         //水平剧中
	        $objPHPExcel->getActiveSheet()->getStyle('C'.$hj.':'.$sumcolm.$hj)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
	            //垂直剧中
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':'.$sumcolm.$hj)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
	        //字体
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':'.$sumcolm.$hj)->getFont()->setSize(11);
	        $objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':'.$sumcolm.$hj)->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        	$objPHPExcel->getActiveSheet()->getStyle('A'.$hj.':'.$sumcolm.$hj)->getFill()->getStartColor()->setARGB('00FFCC99');
        	 $objPHPExcel->getActiveSheet()->getStyle('A'.$hj)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
        	 $objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
         	 for($i=0;$i<count($kaohe);$i++){
         	 	$objPHPExcel->getActiveSheet()->getColumnDimension(chr($i+99))->setWidth(20);
	        }
	        $objPHPExcel->getActiveSheet()->getColumnDimension($sumcolm)->setWidth(20);

	           $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:'.$sumcolm.$hj)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
        
        	  $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
	        header("Pragma: public");
	        header("Expires:0");
	        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
	        header("Content-Type:application/force-download");
	        header("Content-Type:application/vnd.ms-execl");
	        header("Content-Type:application/octet-stream");
	        header("Content-Type:application/download");
	        header('Content-Disposition:attachment;filename='.$biaoti.'.xls');
	        header("Content-type:text/html;charset=utf-8");
	        header("Content-Transfer-Encoding:binary");
	        
	        $objWriter->save('php://output');
	}
	//导出明细
	public function mingxi($aid){
		$apply=Db::table("rc_baoxiao_apply")->where(["id"=>$aid])->find();
		//print_r($apply);
		$apply["cman"]=Db::table("employee")->where(["id"=>$apply["cuser"]])->value("em_name");
		$apply["ctime"]=date("Y-m-d",$apply["ctime"]);
		$tmp=Db::table("cg_kaoheqi2")->alias("k2")
		->join("cg_kaoheqi1 k1","k2.pid=k1.id")
		->join("cg_cainian c","k1.cid=c.id")
		->where("k2.id={$apply["kaohe"]}")
		->field("c.title as nian,k2.title as yue")->find();

		$apply["kaohe"]=$tmp["nian"].'-'.$tmp["yue"];
		$apply["payway"]=Db::table("cg_paymethod")->where(["id"=>$apply["payway"]])->value("title");
		$apply["bman"]=Db::table("employee")->where(["id"=>$apply["baoxiaoren"]])->value("em_name");
		$apply["jrbm"]=Db::table("cg_fijiruzd")->alias("fj")->join("department d", "fj.title=d.id")->where("fj.id={$apply["jrbm"]}")->value("d.de_name");
		$apply["weifujin"]=$apply["pizhunjin"]-$apply["zhifujin"];
		$feiyong=Db::table("cg_feiyongkemu3")->where("id in ({$apply["feis"]})")->select();
		
		$feiyongnum=count($feiyong);
		
		if($apply["chuchai"]){
			
			$apply["chuchai"]=Db::table("chuchai_apply")->where("id in ({$apply["chuchai"]})")->select();
		}
		if($apply["wanneng"]){
			
			$apply["wanneng"]=Db::table("wanneng_apply")->where("id in ({$apply["wanneng"]})")->select();
		}
		
		$applyde=Db::table("rc_baoxiao_detail")->alias("apd")
		->join("cg_jxs j","apd.jxs=j.id","LEFT")
		->join("employee e","apd.shenpiren=e.id","LEFT")
		->where(["aid"=>$aid])
		->field("apd.start,apd.end,apd.chufa,apd.mudi,apd.feiid,apd.feis,apd.pizhunfeiyong,apd.remark,j.title as jxs,e.em_name,apd.state")
		->select();
			$eid=Session::get("employee_id");
    		$users=Db::table("employee")->where(["id"=>$eid])->find();

    	$sumcol=$feiyongnum+5;
		$sumcolm=chr($sumcol+96);
		Vendor("PHPExcel");
        
        
       Vendor("PHPExcel.Writer.Excel5");

       // Loader::import('PHPExcel.Classes.PHPExcel');
        //Loader::import('PHPExcel.Classes.PHPExcel.IOFactory.PHPExcel_IOFactory');
       //  Vendor("Excel.PHPExcel.Writer.Excel2007");
        $objPHPExcel= new PHPExcel();
            
            
        $objProps=$objPHPExcel->getProperties();
            
        $objProps->setCreator('admin');
        $objProps->setLastModifiedBy('admin');
        $objProps->setTitle('Office 2003 XLSX Test Document');
        $objProps->setSubject('Office 2003 XLSX Test Document');
        $objProps->setDescription("Test document, generated by PHPExcel.");
        $objProps->setKeywords("office excel PHPExcel");
        $objProps->setCategory("Test");
            	
        $objPHPExcel->setActiveSheetIndex(0);
        $objPHPExcel->getActiveSheet()->setTitle("日常费用报销申请");


      
        $objPHPExcel->getActiveSheet()->mergeCells('B1:'.$sumcolm.'1');
        $objPHPExcel->getActiveSheet()->setCellValue('A1',"报销单主题");
        $objPHPExcel->getActiveSheet()->setCellValue('B1',$apply["title"],PHPExcel_Cell_DataType::TYPE_STRING);

        $objPHPExcel->getActiveSheet()->mergeCells('D2:'.$sumcolm.'2');
        $objPHPExcel->getActiveSheet()->setCellValue('A2',"单号");
        $objPHPExcel->getActiveSheet()->setCellValue('B2',$apply["no"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValue('C2',"代办人（申请人）");
        $objPHPExcel->getActiveSheet()->setCellValue('D2',$apply["cman"],PHPExcel_Cell_DataType::TYPE_STRING);

        $objPHPExcel->getActiveSheet()->mergeCells('D3:'.$sumcolm.'3');
        $objPHPExcel->getActiveSheet()->setCellValue('A3',"报销归入考核期");
        $objPHPExcel->getActiveSheet()->setCellValue('B3',$apply["kaohe"],PHPExcel_Cell_DataType::TYPE_STRING);
        $objPHPExcel->getActiveSheet()->setCellValue('C3',"报销单创建日期");
        $objPHPExcel->getActiveSheet()->setCellValue('D3',$apply["ctime"],PHPExcel_Cell_DataType::TYPE_STRING);

        $objPHPExcel->getActiveSheet()->mergeCells('B4:'.$sumcolm.'4');
        $objPHPExcel->getActiveSheet()->setCellValue('A4',"报销事由");
        $objPHPExcel->getActiveSheet()->setCellValue('B4',$apply["reason"],PHPExcel_Cell_DataType::TYPE_STRING);

        $objPHPExcel->getActiveSheet()->setCellValue('A5',"报销人");
        $objPHPExcel->getActiveSheet()->setCellValue('B5',"起止时间");
        $objPHPExcel->getActiveSheet()->setCellValue('C5',"出发地点");
        $objPHPExcel->getActiveSheet()->setCellValue('D5',"到达地点");
        for($i=0;$i<count($feiyong);$i++){
        	$objPHPExcel->getActiveSheet()->setCellValue(chr($i+101).'5',$feiyong[$i]["title"]);
        	$feiyong[$i]["jine"]=0;
        	$feiyong[$i]["pizhunjine"]=0;
        }


        $objPHPExcel->getActiveSheet()->setCellValue($sumcolm.'5',"费用说明");
        $objPHPExcel->getActiveSheet()->getStyle('A5:'.$sumcolm.'5')->getFill()->setFillType(PHPExcel_Style_Fill::FILL_SOLID);
        $objPHPExcel->getActiveSheet()->getStyle('A5:'.$sumcolm.'5')->getFill()->getStartColor()->setARGB('0099CCFF');
       	
        $h=5;
        for($i=0;$i<count($applyde);$i++){
        	$h++;$vo=$applyde[$i];
        	$objPHPExcel->getActiveSheet()->getStyle($h)->getAlignment()->setWrapText(true);
        	$objPHPExcel->getActiveSheet()->setCellValue('A'.$h,$apply["bman"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('B'.$h,"从"."{$vo["start"]}\n至{$vo["end"]}");
        	$objPHPExcel->getActiveSheet()->setCellValue('C'.$h,$vo["chufa"],PHPExcel_Cell_DataType::TYPE_STRING);
        	$objPHPExcel->getActiveSheet()->setCellValue('D'.$h,$vo["mudi"],PHPExcel_Cell_DataType::TYPE_STRING);
        	for($j=0;$j<count($feiyong);$j++){
        		if($vo["feiid"]==$feiyong[$j]["id"]){
        			$objPHPExcel->getActiveSheet()->setCellValue(chr($j+101).$h,$vo["feis"].'/'.$vo["pizhunfeiyong"],PHPExcel_Cell_DataType::TYPE_STRING);
        			$feiyong[$j]["jine"]+=$vo["feis"];
        			$feiyong[$j]["pizhunjine"]+=$vo["pizhunfeiyong"];
        		}
        	}

        	$objPHPExcel->getActiveSheet()->setCellValue($sumcolm.$h,$vo["remark"],PHPExcel_Cell_DataType::TYPE_STRING);
        	
        	

        }
        $h++;
        $objPHPExcel->getActiveSheet()->mergeCells('A'.$h.':D'.$h);
        $objPHPExcel->getActiveSheet()->setCellValue('A'.$h,"总计");
        //加粗
        $objPHPExcel->getActiveSheet()->getStyle('A'.$h.':'.$sumcolm.$h)->getFont()->setBold(true);
        for($i=0;$i<count($feiyong);$i++){
        	$objPHPExcel->getActiveSheet()->setCellValue(chr($i+101).$h,$feiyong[$i]["jine"].'/'.$feiyong[$i]["pizhunjine"],PHPExcel_Cell_DataType::TYPE_STRING);
        }

                     //水平剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1:'.$sumcolm.$h)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER);
            //垂直剧中
        $objPHPExcel->getActiveSheet()->getStyle('A1:'.$sumcolm.$h)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
        //字体
        $objPHPExcel->getActiveSheet()->getStyle('A1:'.$sumcolm.$h)->getFont()->setSize(10);



        	$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(20);
         	$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(10);
         	for($i=0;$i<count($feiyong);$i++){
        		$objPHPExcel->getActiveSheet()->getColumnDimension(chr($i+101))->setWidth(10);
        	}
        	$objPHPExcel->getActiveSheet()->getColumnDimension($sumcolm)->setWidth(10);
        	     $styleArray = array(  
        'borders' => array(  
            'allborders' => array(  
                //'style' => PHPExcel_Style_Border::BORDER_THICK,//边框是粗的  
                'style' => PHPExcel_Style_Border::BORDER_THIN,//细边框  
                //'color' => array('argb' => 'FFFF0000'),  
            ),  
        ),  
    );  
   $objPHPExcel->getActiveSheet()->getStyle('A1:'.$sumcolm.$h)->applyFromArray($styleArray);//这里就是画出从单元格A5到N5的边框，看单元格最右边在哪哪
          $objWriter = new PHPExcel_Writer_Excel5($objPHPExcel);
           ob_end_clean();
	        header("Pragma: public");
	        header("Expires:0");
	        header("Cache-Control:must-revalidate, post-check=0, pre-check=0");
	        header("Content-Type:application/force-download");
	        header("Content-Type:application/vnd.ms-execl");
	        header("Content-Type:application/octet-stream");
	        header("Content-Type:application/download");
	        header('Content-Disposition:attachment;filename=日常费用报销申请.xls');
	        header("Content-type:text/html;charset=utf-8");
	        header("Content-Transfer-Encoding:binary");
	        
	        $objWriter->save('php://output');
	}

}